package br.com.leilao.cliente.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.com.leilao.cliente.model.Cliente;
import br.com.leilao.util.ConectaBanco;
import br.com.leilao.util.LeilaocodeException;

public class ClienteDao {

	private final String INSERT_QUERY = "insert into ClienteDB (nome, rg, cpf, dataNasc, endereco, numero, bairro, cidade, uf, telefone, celular, email) values (?,?,?,?,?,?,?,?,?,?,?,?)";
	private final String UPDATE_QUERY = "update ClienteDB set nome = ?, rg = ?, cpf = ?, dataNasc = ?, endereco = ?, numero = ?, bairro = ?, cidade = ?, uf = ?, telefone = ?, celular = ?, email = ? where codigo = ?";
	private final String SELECT_ALL_QUERY = "select * from ClienteDB";
	private final String SELECT_BY_ID_QUERY = "select * from ClienteDB where codigo = ? ";
	private final String DELETE_BY_ID_QUERY = "delete * from ClienteDB where codigo = ? ";	
	
	public void cadastrar(Cliente ob) throws LeilaocodeException, ClassNotFoundException{
		Connection con = null;
		PreparedStatement prepStmt = null;
		try {
			con = ConectaBanco.getConnection();
			prepStmt = con.prepareStatement(INSERT_QUERY);
			prepStmt.setString(1, ob.getNome());
			prepStmt.setLong(2, ob.getRg());
			prepStmt.setString(3, ob.getCpf());
			prepStmt.setString(4, ob.getDataNasc());
			prepStmt.setString(5, ob.getEndereco());
			prepStmt.setString(6, ob.getNumero());
			prepStmt.setString(7, ob.getBairro());
			prepStmt.setString(8, ob.getCidade());
			prepStmt.setString(9, String.valueOf(ob.getUf()));
			prepStmt.setString(10, ob.getTelefone());
			prepStmt.setString(11, ob.getCelular());
			prepStmt.setString(12, ob.getEmail());			
			prepStmt.execute();
		} catch (SQLException e) {
			String msg = "[ClienteDB][cadastrar(Cliente ob)]: " + e.getMessage();
			LeilaocodeException le = new LeilaocodeException(msg, e);
			throw le;
		} finally {
			ConectaBanco.closeAll(con, prepStmt);
		}
	}

	
	public void atualizar(Cliente ob) throws LeilaocodeException, ClassNotFoundException{
		Connection con = null;
		PreparedStatement prepStmt = null;
		try {
			con = ConectaBanco.getConnection();
			prepStmt = con.prepareStatement(UPDATE_QUERY);
			prepStmt.setInt(1, ob.getCodigo());
			prepStmt.setString(2, ob.getNome());
			prepStmt.setLong(3, ob.getRg());
			prepStmt.setString(4, ob.getCpf());
			prepStmt.setString(5, ob.getDataNasc());
			prepStmt.setString(6, ob.getEndereco());
			prepStmt.setString(7, ob.getNumero());
			prepStmt.setString(8, ob.getBairro());
			prepStmt.setString(9, ob.getCidade());
			prepStmt.setString(10, String.valueOf(ob.getUf()));
			prepStmt.setString(11, ob.getTelefone());
			prepStmt.setString(12, ob.getCelular());
			prepStmt.setString(13, ob.getEmail());
			prepStmt.executeUpdate();
		} catch (SQLException e) {
			String msg = "[ClienteDB][atualizar(Cliente ob)]: " + e.getMessage();
			LeilaocodeException le = new LeilaocodeException(msg, e);
			throw le;
		} finally {
			ConectaBanco.closeAll(con, prepStmt);;
		}
	}

	
	public List<Cliente> listar() throws LeilaocodeException, ClassNotFoundException{
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Cliente> listaCliente = new ArrayList<Cliente>();
		try {
			con = ConectaBanco.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(SELECT_ALL_QUERY);
			while (rs.next()) {
				int codigo = rs.getInt("codigo");
				String nome = rs.getString("nome");
				long rg = rs.getLong("rg");
				String cpf = rs.getString("cpf");
				String dataNasc = rs.getString("dataNasc");				
				String endereco = rs.getString("endereco");
				String numero = rs.getString("numero");
				String bairro = rs.getString("bairro");
				String cidade = rs.getString("cidade");
				char uf =  String.valueOf(rs.getString("uf")).charAt(0);
				String telefone = rs.getString("telefone");
				String celular = rs.getString("celular");
				String email = rs.getString("email");
				Cliente c = new Cliente(codigo, nome, rg, cpf, dataNasc, endereco, numero, bairro, cidade, uf, telefone, celular, email);
				listaCliente.add(c);
			}
		} catch (SQLException e) {
			String msg = "[ClienteDB][listar()]: "+ e.getMessage();
			LeilaocodeException le = new LeilaocodeException(msg, e);
			throw le;
		} finally {
			ConectaBanco.closeAll(con, stmt, rs);
		}
		return listaCliente;		
	}

	public Cliente getClienteId(int codigo) throws LeilaocodeException, ClassNotFoundException{
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		Cliente cliente = null;
		try {
			con = ConectaBanco.getConnection();
			prepStmt = con.prepareStatement(SELECT_BY_ID_QUERY);
			prepStmt.setInt(1, codigo);
			rs = prepStmt.executeQuery();
			if (rs.next()) {				
				String nome = rs.getString("nome");
				long rg = rs.getLong("rg");
				String cpf = rs.getString("cpf");
				String dataNasc = rs.getString("dataNasc");				
				String endereco = rs.getString("endereco");
				String numero = rs.getString("numero");
				String bairro = rs.getString("bairro");
				String cidade = rs.getString("cidade");
				char uf =  String.valueOf(rs.getString("uf")).charAt(0);
				String telefone = rs.getString("telefone");
				String celular = rs.getString("celular");
				String email = rs.getString("email");
				cliente = new Cliente(codigo, nome, rg, cpf, dataNasc, endereco, numero, bairro, cidade, uf, telefone, celular, email);
			}
		} catch (SQLException e) {
			String msg = "[ClienteDB][getClienteId(int codigo)]: " + e.getMessage();
			LeilaocodeException le = new LeilaocodeException(msg, e);
			throw le;
		} finally {
			ConectaBanco.closeAll(con, prepStmt, rs);
		}
		return cliente;		
	}	
	
	public void excluir(Cliente ob) throws LeilaocodeException, ClassNotFoundException, SQLException{
		Connection con = ConectaBanco.getConnection();
		PreparedStatement pstm = null;
		try{
			pstm = con.prepareStatement(DELETE_BY_ID_QUERY);
			pstm.setInt(1, ob.getCodigo());
			pstm.execute();
	   }catch(SQLException e){
		   String msg = "[ClienteDB][excluir(Cliente ob)]: " + e.getMessage();
		   LeilaocodeException le = new LeilaocodeException(msg, e);
		   throw le; 
	   }finally {
		   ConectaBanco.closeAll(con, pstm);
		}
	}

}
